package com.why.util;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;

import com.why.entity.IndexData;
import com.why.entity.Org;

public class ExcelUtil {
	
	public static List<IndexData> getIndexDataObj(String path){
		List<IndexData> datas = new ArrayList<IndexData>();
		InputStream is = null;
		try{
			is = new FileInputStream(path);
			Workbook wb = WorkbookFactory.create(is);
			Row row;
	        Cell cell;
	        List<String> list = new ArrayList<String>();
	        Sheet sheet = wb.getSheetAt(0);
	        
	        //年份
	        row = sheet.getRow(0);
	        cell = row.getCell(0);
	        String year = getCellValue(cell);
	        int hear_row_num = 10;
	        boolean flag=false;
	        //表头
	        for (int i = sheet.getFirstRowNum()+1; i < sheet.getLastRowNum(); i++) {
	            row = sheet.getRow(i);
	            for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
	            	cell = row.getCell(j);
	            	String cell_value = getCellValue(cell);
	            	if(flag){
	            		list.add(cell_value.substring(0, cell_value.lastIndexOf(".")));
	            	}
	                if(cell_value.equals("index_title")){
	                	flag = true;
	                	hear_row_num = i+1;
	                }

	            }
	           if(flag)
	        	   break;
	        }
	        //数据
	        
	        for(int i=hear_row_num;i<sheet.getPhysicalNumberOfRows();i++){
	        	row = sheet.getRow(i);
	        	String org_id = null;
	        	for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
	        		cell = row.getCell(j);
	        		String cell_value = getCellValue(cell);
	        		if(j==row.getFirstCellNum()){
	        			org_id = cell_value.substring(0, cell_value.lastIndexOf("."));
	        		}else{
	        			IndexData obj = new IndexData();
	        			obj.setOrg_id(org_id);
	        			obj.setIndex_id(list.get(j-1));
	        			obj.setData_value(cell_value);
	        			obj.setYear(year);
	        			Comment comm = cell.getCellComment();
	        			if(comm!=null){
	        				obj.setData_source(comm.getString().toString());
	        			}
	        			datas.add(obj);
	        		}
	        		
	        	}
	        }
	        
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				is.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		return datas;
	}

	private static String getCellValue(Cell cell){
		String value = "";
		switch (cell.getCellType()) {
	        case Cell.CELL_TYPE_STRING:
	        	value = cell.getRichStringCellValue().getString();
	            break;
	        case Cell.CELL_TYPE_NUMERIC:
	            if (DateUtil.isCellDateFormatted(cell)) {
	            	value = String.valueOf(cell.getDateCellValue());
	            } else {
	            	value = String.valueOf(cell.getNumericCellValue());
	            }
	            break;
	        case Cell.CELL_TYPE_BOOLEAN:
	            value = String.valueOf(cell.getBooleanCellValue());
	            break;
	        case Cell.CELL_TYPE_FORMULA:
	        	value = cell.getCellFormula();
	            break;
	        default:
	            value="";
	    }
		return value;
	}
	
    private static Map<String, CellStyle> createStyles(Workbook wb){
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        DataFormat df = wb.createDataFormat();

        CellStyle style;
        Font headerFont = wb.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = createBorderedStyle(wb);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(headerFont);
        styles.put("header", style);

        Font font1 = wb.createFont();
        font1.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setFont(font1);
        styles.put("cell_a", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setFont(font1);
        styles.put("cell_b", style);
        return styles;
    }
    
    private static CellStyle createBorderedStyle(Workbook wb){
        CellStyle style = wb.createCellStyle();
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        return style;
    }


	public static Workbook exportExcel(List<List<com.why.entity.Cell>> headers,List<List<Object>> rows,
			String year) throws Exception {
		// TODO Auto-generated method stub
		Workbook wb = new HSSFWorkbook();
		Map<String, CellStyle> styles = createStyles(wb);
		Sheet sheet = wb.createSheet(year+"年数据导入模板");
		
		//初始
		sheet.setDefaultColumnWidth(18);
		sheet.setDisplayGridlines(false);
        sheet.setPrintGridlines(false);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);
        sheet.setAutobreaks(true);
        printSetup.setFitHeight((short)1);
        printSetup.setFitWidth((short)1);
        
        List<String> merCell = new ArrayList<String>();
        List<CellRangeAddress> rowRange = new ArrayList<CellRangeAddress>();
        CellStyle style = styles.get("header");
        CellStyle cell_b_style = styles.get("cell_b");
        //标题行
        Row title_row = sheet.createRow(0);
        title_row.setHeight((short) 500);
        CellStyle title_style = createBorderedStyle(wb);
        title_style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        title_style.setAlignment(CellStyle.ALIGN_CENTER);
        title_style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        title_style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		DataFormat format= wb.createDataFormat();
		title_style.setDataFormat(format.getFormat("@\"年数据导入模板\""));
        int max_col = 0;
        for(List<com.why.entity.Cell> temp : headers){
        	int num = temp.size();
        	if(num>max_col)
        		max_col = num;
        }
        for(int i=0;i<max_col;i++){
        	Cell cell = title_row.createCell(i);
        	cell.setCellStyle(title_style);
        	if(i==0){
        		cell.setCellValue(year);
        	}
        }
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,max_col));
        //表头
        for(int i=1;i<=headers.size()-1;i++){
        	List<com.why.entity.Cell> row_data = headers.get(i-1);
        	Row row = sheet.createRow(i);
        	row.setHeight((short) 400);
        	int row_length=0;
        	for(int j=0;j<row_data.size();j++){
        		com.why.entity.Cell cell_data = row_data.get(j);
        		int colspan = cell_data.getColspan();		//列宽
        		int rowspan = cell_data.getRowspan();		//行宽
        		
        		//判断列合并
        		if(merCell.contains(String.valueOf(i)+String.valueOf(row_length))){
        			Cell cell_temp = row.createCell(row_length);
        			cell_temp.setCellStyle(style);
        			row_length++;
        		}
        		
        		if(rowspan>1){
        			rowRange.add(new CellRangeAddress(i,i+rowspan-1,row_length,row_length));
        			for(int t = i+1;t<i+rowspan;t++){
        				merCell.add(String.valueOf(t)+String.valueOf(row_length));
        			}
        		}
        		
        		Cell cell = row.createCell(row_length);
        		cell.setCellStyle(style);
        		cell.setCellValue(cell_data.getText());
        		
        		if(colspan>1){
        			for(int t=row_length+1;t<row_length+colspan;t++){
        				Cell c1 = row.createCell(t);
        				c1.setCellStyle(style);
        			}
        			sheet.addMergedRegion(new CellRangeAddress(i,i,row_length,row_length+cell_data.getColspan()-1));
        		}
        		row_length = row_length+cell_data.getColspan();
        		
        	}
        }
        List<com.why.entity.Cell> row_data = headers.get(headers.size()-1);
        Row last_row = sheet.createRow(headers.size());
        last_row.setHeight((short) 400);
    	int row_length=0;
    	for(int j=0;j<row_data.size();j++){
    		com.why.entity.Cell cell_data = row_data.get(j);
    		//判断列合并
    		if(merCell.contains(String.valueOf(headers.size()-1)+String.valueOf(row_length))){
    			Cell cell_temp = last_row.createCell(row_length);
    			cell_temp.setCellStyle(style);
    			cell_temp.setCellValue("index_title");
    			row_length++;
    		}
    		
    		CellStyle temp_style = createBorderedStyle(wb);
    		temp_style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    		temp_style.setAlignment(CellStyle.ALIGN_CENTER);
    		temp_style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    		temp_style.setFillPattern(CellStyle.SOLID_FOREGROUND);
//    		DataFormat format= wb.createDataFormat();
    		temp_style.setDataFormat(format.getFormat("[="+cell_data.getId()+"]\""+cell_data.getText()+"\";General"));
    		Cell cell = last_row.createCell(row_length);
    		cell.setCellStyle(temp_style);
    		cell.setCellValue(Integer.valueOf(cell_data.getId()));
    		
    		row_length++;
    	}
        for(CellRangeAddress c:rowRange){
        	sheet.addMergedRegion(c);
        }
        
        Drawing p=sheet.createDrawingPatriarch();
        int header_size = headers.size();
        //行信息
        for(int n=0;n<rows.size();n++){
        	List<Object> row = rows.get(n);
        	Org org = (Org) row.get(0);
        	Row r = sheet.createRow(n+header_size+1);
        	//临时
        	CellStyle temp_style = createBorderedStyle(wb);
        	temp_style.setAlignment(CellStyle.ALIGN_LEFT);
//        	DataFormat format= wb.createDataFormat();
        	temp_style.setDataFormat(format.getFormat("[="+org.getId()+"]\""+org.getOrg_name()+"\""));
        	Cell org_cell = r.createCell(0);
        	org_cell.setCellStyle(temp_style);
        	org_cell.setCellValue(Integer.valueOf(org.getId()));
        	
        	for(int i=1;i<row.size();i++){
        		IndexData item = (IndexData) row.get(i);
        		Cell data_cell = r.createCell(i);
        		data_cell.setCellStyle(cell_b_style);
        		if(item!=null){
        			data_cell.setCellValue(Integer.valueOf(item.getData_value()));
        			String data_source = item.getData_source();
        			if(data_source!=null && !data_source.equals("")){
        				Comment comm = p.createCellComment(new HSSFClientAnchor(0,0,0,0,
        						(short)(i+1),n+header_size+2,(short)(i+2),n+header_size+5));
        				comm.setString(new HSSFRichTextString(data_source));
        				data_cell.setCellComment(comm);
        			}
        		}
        		
        	}
        }
        return wb;
        
//        FileOutputStream fOut = new FileOutputStream("C:/test-1.xls");
//        wb.write(fOut); 
//        fOut.flush();
//        fOut.close();
//        System.out.println("文件生成..."); 

	}
	
	
}
